Supply Bulk Costing Data from a Spreadsheet

You can supply data to bulk costing using a spreadsheet. For example, if you are a sourcing engineer managing specific groups of commodity parts, you can store the information for these parts in Excel spreadsheets for easy access and updates.

You can then use aP Pro to load and analyze these spreadsheets, giving you quick and powerful analysis functions.

This spreadsheet-based method is more efficient than Bulk Costing Using the UI, when dealing with high volumes of parts and changes - see When to use Spreadsheets for more details

Tip: See the Perform Bulk Costing with User Inputs Sheets aP Academy training course for more details.

Note: The functionality of these spreadsheets can vary depending on how you configure the interaction of bulk costing with your own internal data sources such as PLM or MRP systems.

Introduction to User Inputs Sheets

You can use Bulk Costing with User Inputs Sheets - spreadsheets in Excel or CSV format, containing parts, production information, overrides for CSL values, and other cost inputs.

For example:

These sheets provide a quick way to retrieve large sets of information from pre-existing data into the bulk costing process, avoiding any need to replicate this information.

Advanced Functions

You can also set up advanced functions:

  • Setting Process Setup Option columns options, for example the number of cavities for an injection moulded part.

  • Using Excel functions such as macros to drive bulk costing activities, for example, to link to UDAs.

Note: Spreadsheet values override values from other sources. See Precedence of production input overrides.

When to use Spreadsheets

User Inputs Sheets require setup and maintenance, so you should decide whether this is worthwhile for your organization. Typically, this method is best if you have large numbers of components, and high frequencies of production change.

For example, if your organization has a large library containing thousands of parts, with your production parameters changing significantly for different process groups, or if you wish to use advanced features such as Excel macros, User Input Sheets are worth considering.

Note: Contact your aPriori customer success manager for further guidance.

Process Overview

Tip: See Bulk Costing Using the UI for details of specific fields.

  1. Create a new bulk cost group as usual, with relevant values in the Bulk Costing & Analysis UI, and click Cost to create the baseline for your imported components.

  2. Save your details in a User Inputs sheet (Excel spreadsheet), with the columns in the spreadsheet files mapping to the columns in the Bulk Costing & Analysis UI table.

  3. Edit this information in the spreadsheet created.

  4. Import the edited file and run costing on the components using bulk costing, specifying this file in the Inputs File, fieldBulk Costing Using the UI

Example

Request

You have been sent an Excel data file from your engineering manager concerning a list of sheet metal parts produced by the Only Sheet Metal Inc supplier.

These parts form an assembly that will be used within a new product coming to market; you have also been given all the CAD files for these components. As the physical assembly develops your manager tasks you with ensuring that the data is kept up to date to ensure costing runs alongside the prototype production.

Requirements: Quick, Global Updates

Your manager asks you to be ready to quickly:

  • Update annual volume changes for all these components

  • Make changes to the material choices for each component; for example, if some components fail testing and need to be manufactured from a more robust option.

Solution: User Input Sheets

You can use user input sheets to import the Excel data supplied from the engineering manager, which reflects the current production information concerning the components.

Follow this process (see Bulk Costing Using the UI for more details of specific fields):

  1. Create a Bulk Costing group for this template:

  2. Edit component information as needed:

  3. Click Cost to create the baseline for all components.

  4. Click File > Export Inputs File to export this as your template input user sheet form

Test the Template

With the template created, you can test its functionality.

  • For the first eight parts you want to change the annual volume to 11,000 units.

  • For the next four parts, you want to change the material to steel cold worked AISI 1020.

  • And for the last four parts you want to change the annual volume to 20,000 units and change the material to 304 stainless steel.

To follow this process with Inputs User Sheets:

  1. Open the exported Excel file, and make all these changes in this file:

    Note: Material names in the spreadsheet must exactly match the material names in aPriori.

  2. Before saving the updated Excel sheet, update the scenario name, to avoid override the existing one when you run bulk costing. Change the scenario name to Only Sheet Metal Inc Updated for all entries in the scenario column:

  3. Save the updated Excel file, then return to aP Pro, and create a new Bulk Cost Group, selecting the updated Excel sheet as you Inputs file and giving a new name for a roll-up to view after costing:

  4. Review the data then click Cost to cost these components:

  5. When costing is complete, click OK and the roll-up will open for review:

  6. From this roll-up, you can export a summary sheet and compare results side by side.